﻿using Apps.Common;
using Apps.Models;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Apps.DAL.ERP
{
    public partial class ERP_Repository : DbContext<YANX_RECORD>
    {
        public DataTable GetSamplingData(string batchType, string samplingStartTime, string samplingEndTime, string cph, string SPARESTR1 = "", string MATERIEL_NAME = "", string MATCH_DATE = "")
        {
            string SQL = @"
                            SELECT * FROM (
                            SELECT Distinct
						    SELF_SAMPLING_RC_RELATED.ISJSUAN,
						    SELF_SAMPLING_RC_RELATED.RC_BATCH_CODE,
						    NVL(V_RC_SAMPLING.BATCH_TYPE,'自采煤') BATCH_TYPE,
                            V_LMS_QUERY.BATCH_CODE,
                            V_LMS_QUERY.MAKE_CODE,
                            V_LMS_QUERY.TEST_CODE,
                            V_RC_SAMPLING.MATERIEL_ID,
                            V_LMS_QUERY.MATERIEL_NAME,
                            V_LMS_QUERY.MATERIEL_TYPE_NAME,
                            V_RC_SAMPLING.MINE_ID,
                            V_LMS_QUERY.MINE_NAME,
                            V_RC_SAMPLING.SUPPLIER_ID,
                            V_LMS_QUERY.SUPPLIER_NAME,
                            V_RC_SAMPLING.TRANSPORT_ID,
                            V_LMS_QUERY.TRANSPORT_NAME,
                            V_LMS_QUERY.MATCH_TIME,
                            V_RC_SAMPLING.LPR,
                            V_RC_SAMPLING.SAMPLING_BEGIN_TIME,
                            V_RC_SAMPLING.SAMPLING_END_TIME,
                            V_RC_SAMPLING.RFID,
                            V_RC_SAMPLING.SPARESTR1,
						    V_LMS_QUERY.AUDIT_TIME
                            FROM V_LMS_QUERY 
						    LEFT JOIN V_RC_SAMPLING ON V_LMS_QUERY.BATCH_CODE = V_RC_SAMPLING.BATCH_CODE 
						    LEFT JOIN SELF_SAMPLING_RC_RELATED ON SELF_SAMPLING_RC_RELATED.RC_BATCH_CODE = V_LMS_QUERY.BATCH_CODE
                            ) T     
                            %WHERE%
						    ORDER BY BATCH_CODE DESC,SAMPLING_BEGIN_TIME ASC ";


            SQL = $@"SELECT Distinct * FROM (
                        SELECT Distinct 
                        V_RC_SAMPLING.BATCH_TYPE,
                        to_char(V_LMS_QUERY.MATCH_TIME,'YYYY-MM-DD') MATCH_DATE,
                        V_LMS_QUERY.MATCH_TIME,
                        NVL(LMS_COLLECTSAMPLE.TEST_CODE,PLUS.TEST_CODE) TEST_CODE,
                        V_RC_SAMPLING.BATCH_CODE,
                        V_RC_SAMPLING.RFID,
                        V_RC_SAMPLING.SAMPLE_PAIL,
                        V_RC_SAMPLING.LPR,
                        V_RC_SAMPLING.SAMPLING_BEGIN_TIME,
                        V_RC_SAMPLING.SAMPLING_END_TIME,
                        V_RC_SAMPLING.SPARESTR1,
                        NULL AS MINE_NAME,
                        to_char(V_RC_SAMPLING.MATERIEL_ID) MATERIEL_ID,
                        to_char(V_RC_SAMPLING.MATERIEL_NAME) MATERIEL_NAME,
                        V_RC_SAMPLING.SUPPLIER_NAME,
                        V_RC_SAMPLING.TRANSPORT_NAME,
                        NULL AS MATERIEL_TYPE_NAME,
						V_LMS_QUERY.AUDIT_TIME
                        FROM V_RC_SAMPLING 
                        LEFT JOIN S_COLLECTSAMPLE ON V_RC_SAMPLING.BATCH_CODE = S_COLLECTSAMPLE.BATCH_CODE AND V_RC_SAMPLING.RFID = S_COLLECTSAMPLE.RFID
                        LEFT JOIN LMS_COLLECTSAMPLE ON LMS_COLLECTSAMPLE.MAKE_CODE = S_COLLECTSAMPLE.SAMPLE_CODE
                        LEFT JOIN (SELECT BATCH_CODE,MAX(TEST_CODE) TEST_CODE FROM LMS_COLLECTSAMPLE GROUP BY BATCH_CODE ) PLUS ON PLUS.BATCH_CODE = V_RC_SAMPLING.BATCH_CODE
                        INNER JOIN SELF_SAMPLING_RC_RELATED ON V_RC_SAMPLING.BATCH_CODE = SELF_SAMPLING_RC_RELATED.RC_BATCH_CODE
												INNER JOIN V_LMS_QUERY ON V_LMS_QUERY.TEST_CODE = PLUS.TEST_CODE 
                        WHERE 1=1
                        AND SELF_SAMPLING_RC_RELATED.ISJSUAN = '是'
                        AND NVL(ISERROR,0) <> 1
                        UNION  

                        SELECT Distinct
                        NVL(V_RC_SAMPLING.BATCH_TYPE,'自采煤') BATCH_TYPE,
                        to_char(V_LMS_QUERY.MATCH_TIME,'YYYY-MM-DD') MATCH_DATE,
                        V_LMS_QUERY.MATCH_TIME,
                        V_LMS_QUERY.TEST_CODE,
                        V_LMS_QUERY.BATCH_CODE,
                        null AS RFID,
                        null AS SAMPLE_PAIL,
                        null AS LPR,
                        AUDIT_TIME AS SAMPLING_BEGIN_TIME,
                        AUDIT_TIME AS SAMPLING_END_TIME,
                        null AS SPARESTR1,
                        V_LMS_QUERY.MINE_NAME,
                        to_char(V_LMS_QUERY.MATERIEL_ID) MATERIEL_ID,
                        to_char(V_LMS_QUERY.MATERIEL_NAME) MATERIEL_NAME,
                        V_LMS_QUERY.SUPPLIER_NAME,
                        V_LMS_QUERY.TRANSPORT_NAME,
                        V_LMS_QUERY.MATERIEL_TYPE_NAME,
						V_LMS_QUERY.AUDIT_TIME
                        FROM V_LMS_QUERY 
                        LEFT JOIN V_RC_SAMPLING ON V_LMS_QUERY.BATCH_CODE = V_RC_SAMPLING.BATCH_CODE
                        INNER JOIN SELF_SAMPLING_RC_RELATED ON V_LMS_QUERY.BATCH_CODE = SELF_SAMPLING_RC_RELATED.RC_BATCH_CODE
                        WHERE V_RC_SAMPLING.BATCH_TYPE IS NULL
                        AND SELF_SAMPLING_RC_RELATED.ISJSUAN = '是'
                        ) TT
                        %WHERE%
						ORDER BY BATCH_CODE DESC,SAMPLING_BEGIN_TIME ASC ";
            string WHERE = " WHERE 1=1 ";
            if (!string.IsNullOrEmpty(samplingStartTime) && !string.IsNullOrEmpty(samplingEndTime))
            {

                //NVL(V_RC_SAMPLING.SAMPLING_BEGIN_TIME, V_LMS_QUERY.MATCH_TIME) SAMPLING_BEGIN_TIME,
                //            NVL(V_RC_SAMPLING.SAMPLING_END_TIME, V_LMS_QUERY.MATCH_TIME) SAMPLING_END_TIME,

                //WHERE += $" AND V_RC_SAMPLING.SAMPLING_BEGIN_TIME BETWEEN TO_DATE('{samplingStartTime.ObjToDate()}'，'YYYY-MM-DD hh24:mi:ss') AND TO_DATE('{samplingEndTime.ObjToDate()}'，'YYYY-MM-DD hh24:mi:ss') ";
                WHERE += $" AND SAMPLING_BEGIN_TIME BETWEEN TO_DATE('{samplingStartTime.ObjToDate()}'，'YYYY-MM-DD hh24:mi:ss') AND TO_DATE('{samplingEndTime.ObjToDate()}'，'YYYY-MM-DD hh24:mi:ss') ";

                //AND V_RC_SAMPLING.SAMPLING_BEGIN_TIME BETWEEN TO_DATE('2023-10-01 00:00:01'，'YYYY-MM-DD hh24:mi:ss') AND TO_DATE('2023-10-17 23:59:59'，'YYYY-MM-DD hh24:mi:ss')
                //WHERE += $" AND V_RC_SAMPLING.SAMPLING_BEGIN_TIME BETWEEN TO_DATE('{samplingStartTime.ObjToDate()}'，'YYYY-MM-DD hh24:mi:ss') AND TO_DATE('{samplingEndTime.ObjToDate()}'，'YYYY-MM-DD hh24:mi:ss') ";
                //WHERE += " AND V_RC_SAMPLING.SAMPLING_BEGIN_TIME BETWEEN @SamplingStartTime AND @SamplingEndTime ";
            }
            if (!string.IsNullOrEmpty(MATCH_DATE))
            {
                WHERE += $"  AND MATCH_DATE = @MATCH_DATE ";
            }
            if (!string.IsNullOrEmpty(cph))
            {
                WHERE += "  AND LPR = @LPR ";
            }
            if (!string.IsNullOrEmpty(SPARESTR1))
            {
                WHERE += "  AND SPARESTR1 = @SPARESTR1 ";
            }
            if (!string.IsNullOrEmpty(MATERIEL_NAME))
            {
                WHERE += "  AND MATERIEL_NAME = @MATERIEL_NAME ";
                WHERE += "  AND MATERIEL_TYPE_NAME = '煤场煤' ";
            }
            if (!string.IsNullOrEmpty(batchType))
            {
                WHERE += "  AND BATCH_TYPE = @BATCH_TYPE ";
            }

            SQL = SQL.Replace("%WHERE%", WHERE);
            var data = Db.Ado.GetDataTable(SQL, new SugarParameter[] {
                //new SugarParameter("@SamplingStartTime", samplingStartTime.ObjToDate(),System.Data.DbType.DateTime),
                //new SugarParameter("@SamplingEndTime", samplingEndTime.ObjToDate(),System.Data.DbType.DateTime),
                new SugarParameter("@LPR", cph),
                new SugarParameter("@BATCH_TYPE", batchType),
                new SugarParameter("@MATCH_DATE", MATCH_DATE),
                new SugarParameter("@MATERIEL_NAME", MATERIEL_NAME),
                new SugarParameter("@SPARESTR1", SPARESTR1)});
            return data;

        }

        public DataTable GetSamplingData(string sparestr1)
        {
            string SQL = @"SELECT * FROM (
                        SELECT Distinct
                        SELF_SAMPLING_RC_RELATED.ISJSUAN,
                        SELF_SAMPLING_RC_RELATED.RC_BATCH_CODE,
                        NVL(V_RC_SAMPLING.BATCH_TYPE,'自采煤') BATCH_TYPE,
                        V_LMS_QUERY.BATCH_CODE,
                        V_LMS_QUERY.MAKE_CODE,
                        V_LMS_QUERY.TEST_CODE,
                        V_RC_SAMPLING.MATERIEL_ID,
                        V_LMS_QUERY.MATERIEL_NAME,
                        V_LMS_QUERY.MATERIEL_TYPE_NAME,
                        V_RC_SAMPLING.MINE_ID,
                        V_LMS_QUERY.MINE_NAME,
                        V_RC_SAMPLING.SUPPLIER_ID,
                        V_LMS_QUERY.SUPPLIER_NAME,
                        V_RC_SAMPLING.TRANSPORT_ID,
                        V_LMS_QUERY.TRANSPORT_NAME,
                        V_LMS_QUERY.MATCH_TIME,
                        V_RC_SAMPLING.LPR,
                        V_RC_SAMPLING.SAMPLING_BEGIN_TIME,
                        V_RC_SAMPLING.SAMPLING_END_TIME,
                        V_RC_SAMPLING.RFID,
                        V_RC_SAMPLING.SPARESTR1,
                        V_LMS_QUERY.AUDIT_TIME
                        FROM V_LMS_QUERY 
                        LEFT JOIN V_RC_SAMPLING ON V_LMS_QUERY.BATCH_CODE = V_RC_SAMPLING.BATCH_CODE 
                        LEFT JOIN SELF_SAMPLING_RC_RELATED ON SELF_SAMPLING_RC_RELATED.RC_BATCH_CODE = V_LMS_QUERY.BATCH_CODE
                        ) T 
                        WHERE SPARESTR1 = @SPARESTR1 AND ISJSUAN = '是'
                        ORDER BY BATCH_CODE DESC,SAMPLING_BEGIN_TIME ASC";
            var data = Db.Ado.GetDataTable(SQL, new SugarParameter[] {
                //new SugarParameter("@SamplingStartTime", samplingStartTime.ObjToDate(),System.Data.DbType.DateTime),
                //new SugarParameter("@SamplingEndTime", samplingEndTime.ObjToDate(),System.Data.DbType.DateTime),
                new SugarParameter("@SPARESTR1", sparestr1)});
            return data;

        }

        /// <summary>
        /// 获取物流信息表中 煤种与代码对应表
        /// </summary>
        /// <returns></returns>
        public DataTable GetErpSpecifications()
        {
            string SQL = @"
                    select
		            t.code,t.specification,left(t.code,4),t.sparestr1,'t',left(t.specification,len(t.specification)-len(t1.product)) as origin,'N','物流同步',getdate() 
		            from [SpecifList] t left join [ProductList] t1
		            on t1.code = left(t.code,4)
		            where len(t.code)=7 and t.code like '04%' and t.specification not like '%退%'
		            ";
            var sqlserverHelper = new SqlDbContext().SQLDb;
            var data = sqlserverHelper.Ado.GetDataTable(SQL, new SugarParameter[] { });
            return data;
        }

        /// <summary>
        /// 根据物流流程编码 获取 入厂车辆  对应的煤种 以及 日期
        /// </summary>
        /// <param name="sparestr1"></param>
        /// <returns></returns>
        public DataTable GetErpCarInfoFromSparestr1(string sparestr1)
        {
            string SQL = @"
                    SELECT specification,firstdatetime FROM Trade WHERE sparestr1 = @sparestr1
		            ";
            var sqlserverHelper = new SqlDbContext().SQLDb;
            var data = sqlserverHelper.Ado.GetDataTable(SQL, new SugarParameter[] {
                    new SugarParameter("@sparestr1",sparestr1)
            });
            return data;
        }

        public DataTable GetTestData(string testCode)
        {
            string SQL = @"SELECT
                            SELF_SAMPLING_RC_RELATED.RC_BATCH_CODE,
                            SELF_SAMPLING_RC_RELATED.ISJSUAN,
                            BATCH_CODE,
                            TEST_CODE, 
                            INDEX_ID,
                            INDEX_CODE,
                            INDEX_NAME,
                            INDEX_TYPE,
                            INDEX_UNIT,
                            INDEX_DISNAME,
                            TEST_VALUE,
                            CONFIRMED_TIME
                            FROM V_LMS_TESTDATA_CONFIRMED LEFT JOIN SELF_SAMPLING_RC_RELATED 
                            ON V_LMS_TESTDATA_CONFIRMED.BATCH_CODE = SELF_SAMPLING_RC_RELATED.RC_BATCH_CODE
                            WHERE 1=1 
                            
                            AND TEST_CODE = @TEST_CODE
                            AND STYPE = 0 AND TEST_TYPE = 0 AND TEST_MODE != 2 AND TEST_STATE = 1 AND VALUE_SHOW = 1";
            //AND SELF_SAMPLING_RC_RELATED.ISJSUAN = '是' 结算在前面校验了  这里不做校验
            var data = Db.Ado.GetDataTable(SQL, new SugarParameter[] {
                new SugarParameter("@TEST_CODE", testCode)});
            return data;
        }

        /// <summary>
        /// 是否已经结算
        /// </summary>
        /// <param name="testCode"></param>
        /// <returns></returns>
        public bool IsJieSuan(string testCode)
        {
            var data = Db.Ado.GetDataTable(@"SELECT * FROM SELF_SAMPLING_RC_RELATED 
                                    WHERE RC_BATCH_CODE = (SELECT MAX(BATCH_CODE) FROM V_LMS_TESTDATA_CONFIRMED WHERE TEST_CODE = @TEST_CODE)
                                    AND ISJSUAN = '是'", new SugarParameter[] {
                new SugarParameter("@TEST_CODE", testCode)});
            if (data == null) return false;
            if (data.Rows.Count > 0) return true;
            return false;
        }
    }

    public partial class ERP_Repository : DbContext<YANX_RECORD>
    {

    }
}
